import os
import queue
import pyodbc
import logging
import tkinter as tk
from tkinter import ttk, scrolledtext, messagebox
import threading
import schedule
import time
import configparser
from datetime import datetime
from concurrent.futures import ThreadPoolExecutor

# 日志配置
logging.basicConfig(filename='sync_errors.log', level=logging.ERROR,
                    format='%(asctime)s - %(levelname)s - 源表: %(source_table)s - 错误信息: %(message)s')

# 批量插入的行数
BATCH_SIZE = 1000


class DatabaseSyncApp:
	def __init__(self, root):
		self.root = root
		self.root.title("数据库同步工具")
		self.root.geometry("700x650")
		# 窗体第一次出现的位置 在屏幕的中间
		self.root.update_idletasks()
		x = (self.root.winfo_screenwidth() - self.root.winfo_reqwidth()) / 2
		y = (self.root.winfo_screenheight() - self.root.winfo_reqheight()) / 2
		self.root.geometry("+%d+%d" % (x, y))
		# 配置文件设置
		self.config_file = r"C:\Config.txt"
		self.check_config_file()
		
		# 初始化样式
		self.style = ttk.Style()
		self.style.theme_use('clam')
		
		# 任务控制
		self.scheduler_running = False
		self.schedule_thread = None
		self.log_queue = queue.Queue()
		
		# 创建界面组件
		self.create_widgets()
		
		# 加载配置
		self.load_config()
		
		# 启动日志处理
		self.root.after(100, self.process_log_queue)
	
	def check_config_file(self):
		"""检查并创建配置文件"""
		try:
			if not os.path.exists(self.config_file):
				with open(self.config_file, 'w', encoding='utf-8') as f:
					f.write("[DEFAULT]\n")
				self.log("配置文件已创建于C盘根目录")
		except PermissionError:
			messagebox.showerror("权限错误", "无法在C盘创建配置文件，请以管理员身份运行")
			self.root.destroy()
		except Exception as e:
			messagebox.showerror("配置错误", f"配置文件初始化失败: {str(e)}")
			self.root.destroy()
	
	def create_widgets(self):
		"""创建界面组件"""
		main_frame = ttk.Frame(self.root, padding=10)
		main_frame.pack(fill=tk.BOTH, expand=True)
		
		# 源数据库配置
		src_db_frame = ttk.LabelFrame(main_frame, text="源数据库配置", padding=10)
		src_db_frame.grid(row=0, column=0, padx=5, pady=5, sticky=tk.W + tk.E)
		
		ttk.Label(src_db_frame, text="服务器:").grid(row=0, column=0, sticky=tk.W)
		self.src_server = ttk.Entry(src_db_frame, width=30)
		self.src_server.grid(row=0, column=1, padx=5, pady=2)
		
		ttk.Label(src_db_frame, text="数据库:").grid(row=1, column=0, sticky=tk.W)
		self.src_database = ttk.Entry(src_db_frame, width=30)
		self.src_database.grid(row=1, column=1, padx=5, pady=2)
		
		ttk.Label(src_db_frame, text="用户名:").grid(row=2, column=0, sticky=tk.W)
		self.src_username = ttk.Entry(src_db_frame, width=30)
		self.src_username.grid(row=2, column=1, padx=5, pady=2)
		
		ttk.Label(src_db_frame, text="密码:").grid(row=3, column=0, sticky=tk.W)
		self.src_password = ttk.Entry(src_db_frame, width=30, show="*")
		self.src_password.grid(row=3, column=1, padx=5, pady=2)
		
		# 目标数据库配置
		dest_db_frame = ttk.LabelFrame(main_frame, text="目标数据库配置", padding=10)
		dest_db_frame.grid(row=0, column=1, padx=5, pady=5, sticky=tk.W + tk.E)
		
		ttk.Label(dest_db_frame, text="服务器:").grid(row=0, column=0, sticky=tk.W)
		self.dest_server = ttk.Entry(dest_db_frame, width=30)
		self.dest_server.grid(row=0, column=1, padx=5, pady=2)
		
		ttk.Label(dest_db_frame, text="数据库:").grid(row=1, column=0, sticky=tk.W)
		self.dest_database = ttk.Entry(dest_db_frame, width=30)
		self.dest_database.grid(row=1, column=1, padx=5, pady=2)
		
		ttk.Label(dest_db_frame, text="用户名:").grid(row=2, column=0, sticky=tk.W)
		self.dest_username = ttk.Entry(dest_db_frame, width=30)
		self.dest_username.grid(row=2, column=1, padx=5, pady=2)
		
		ttk.Label(dest_db_frame, text="密码:").grid(row=3, column=0, sticky=tk.W)
		self.dest_password = ttk.Entry(dest_db_frame, width=30, show="*")
		self.dest_password.grid(row=3, column=1, padx=5, pady=2)
		
		# 表配置
		table_frame = ttk.LabelFrame(main_frame, text="表配置", padding=10)
		table_frame.grid(row=1, column=0, columnspan=2, padx=5, pady=5, sticky=tk.W + tk.E)
		
		ttk.Label(table_frame, text="源表名:").grid(row=0, column=0, sticky=tk.W)
		self.source_table = ttk.Entry(table_frame, width=30)
		self.source_table.grid(row=0, column=1, padx=5, pady=2)
		
		ttk.Label(table_frame, text="目标表名:").grid(row=0, column=2, sticky=tk.W)
		self.target_table = ttk.Entry(table_frame, width=30)
		self.target_table.grid(row=0, column=3, padx=5, pady=2)
		
		# 定时配置
		schedule_frame = ttk.LabelFrame(main_frame, text="定时配置", padding=10)
		schedule_frame.grid(row=2, column=0, columnspan=2, padx=5, pady=5, sticky=tk.W + tk.E)
		
		ttk.Label(schedule_frame, text="执行频率:").grid(row=0, column=0, sticky=tk.W)
		self.frequency = ttk.Combobox(schedule_frame, values=["立即执行", "每天", "每小时"], width=10)
		self.frequency.grid(row=0, column=1, padx=5, pady=2)
		self.frequency.set("立即执行")
		
		ttk.Label(schedule_frame, text="执行时间:").grid(row=0, column=2, sticky=tk.W)
		self.schedule_time = ttk.Entry(schedule_frame, width=10)
		self.schedule_time.grid(row=0, column=3, padx=5, pady=2)
		self.schedule_time.insert(0, "00:00")
		
		# 控制按钮
		btn_frame = ttk.Frame(main_frame)
		btn_frame.grid(row=3, column=0, columnspan=2, pady=10)
		
		self.start_btn = ttk.Button(btn_frame, text="开始同步", command=self.start_sync)
		self.start_btn.pack(side=tk.LEFT, padx=5)
		
		self.stop_btn = ttk.Button(btn_frame, text="停止同步", command=self.stop_sync, state=tk.DISABLED)
		self.stop_btn.pack(side=tk.LEFT, padx=5)
		
		# 日志区域
		log_frame = ttk.LabelFrame(main_frame, text="操作日志", padding=10)
		log_frame.grid(row=4, column=0, columnspan=2, padx=5, pady=5, sticky=tk.W + tk.E + tk.N + tk.S)
		
		self.log_text = scrolledtext.ScrolledText(log_frame, width=90, height=15)
		self.log_text.pack(fill=tk.BOTH, expand=True)
		
		# 配置标签颜色
		self.log_text.tag_config('error', foreground='red')
		self.log_text.tag_config('info', foreground='blue')
	
	def get_connection(self, server, database, username, password):
		"""获取数据库连接"""
		driver = self.get_available_driver()
		if not driver:
			raise Exception("未找到合适的ODBC驱动")
		return pyodbc.connect(
			f"DRIVER={{{driver}}};SERVER={server};DATABASE={database};UID={username};PWD={password}"
		)
	
	def get_available_driver(self):
		"""获取可用驱动"""
		drivers = pyodbc.drivers()
		preferred = ["ODBC Driver 17 for SQL Server", "SQL Server Native Client 11.0"]
		for d in preferred:
			if d in drivers:
				return d
		return None
	
	def get_identity_column(self, conn, table_name):
		"""获取表的标识列"""
		query = f"""
            SELECT COLUMN_NAME
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_NAME = '{table_name}'
            AND COLUMNPROPERTY(
                OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME),
                COLUMN_NAME, 'IsIdentity'
            ) = 1
        """
		try:
			with conn.cursor() as cur:
				cur.execute(query)
				result = cur.fetchone()
				return result[0] if result else None
		except:
			return None
	
	def sync_data(self):
		"""执行数据同步"""
		try:
			src_conn = self.get_connection(
				self.src_server.get(),
				self.src_database.get(),
				self.src_username.get(),
				self.src_password.get()
			)
			
			dest_conn = self.get_connection(
				self.dest_server.get(),
				self.dest_database.get(),
				self.dest_username.get(),
				self.dest_password.get()
			)
			
			with src_conn.cursor() as src_cur:
				# 获取源表列信息（带数据类型）
				src_cur.execute(f"SELECT * FROM {self.source_table.get()} WHERE 1=0")
				src_columns = [column[0] for column in src_cur.description]
				
				# 获取目标表元数据
				dest_cur = dest_conn.cursor()
				dest_cur.execute(f"SELECT * FROM {self.target_table.get()} WHERE 1=0")
				dest_columns = [column[0] for column in dest_cur.description]
				dest_cur.close()
				
				# 获取目标表标识列
				identity_col = self.get_identity_column(dest_conn, self.target_table.get())
				
				# 列匹配验证
				if identity_col:
					# 当存在标识列时，自动处理列对应关系
					src_columns = [col for col in src_columns if col != identity_col]
					dest_columns = [col for col in dest_columns if col != identity_col]
					
					if len(src_columns) != len(dest_columns):
						raise ValueError("源表和目标表非标识列数量不一致")
					
					# 验证列顺序和数据类型
					self.validate_columns(src_conn, dest_conn, src_columns, dest_columns)
					
					# 构建排除标识列的SELECT语句
					col_list = ", ".join(src_columns)
					src_cur.execute(f"SELECT {col_list} FROM {self.source_table.get()}")
				else:
					# 普通全量同步
					src_cur.execute(f"SELECT * FROM {self.source_table.get()}")
				
				# 批量插入数据
				self.batch_insert_data(src_cur, dest_conn, dest_columns)
			
			self.log(f"同步成功！")
			messagebox.showinfo("成功", "数据同步完成！")
		
		except Exception as e:
			self.log(f"同步失败：{str(e)}", error=True)
			logging.error(str(e), extra={'source_table': self.source_table.get()})
			messagebox.showerror("错误", f"同步失败：{str(e)}")
		finally:
			try:
				src_conn.close()
				dest_conn.close()
			except:
				pass
	
	def batch_insert_data(self, src_cur, dest_conn, dest_columns):
		"""批量插入数据"""
		insert_columns = dest_columns
		placeholders = ", ".join(["?"] * len(insert_columns))
		insert_sql = f"""
            INSERT INTO {self.target_table.get()}
            ({', '.join(insert_columns)})
            VALUES ({placeholders})
        """
		
		with dest_conn.cursor() as dest_cur:
			# 更安全的清空方式（保留自增计数）
			dest_cur.execute(f"DELETE FROM {self.target_table.get()}")
			dest_conn.commit()
			
			batch = []
			row_count = 0
			while True:
				rows = src_cur.fetchmany(BATCH_SIZE)
				if not rows:
					break
				batch.extend(rows)
				row_count += len(rows)
				if len(batch) >= BATCH_SIZE:
					dest_cur.executemany(insert_sql, batch)
					dest_conn.commit()
					batch = []
					self.log(f"已传输 {row_count} 条记录")
			
			if batch:
				dest_cur.executemany(insert_sql, batch)
				dest_conn.commit()
				self.log(f"已传输 {row_count} 条记录")
	
	def validate_columns(self, src_conn, dest_conn, src_columns, dest_columns):
		"""验证列定义是否兼容"""
		# 获取源表列元数据
		src_meta = self.get_columns_metadata(src_conn, self.source_table.get())
		# 获取目标表列元数据
		dest_meta = self.get_columns_metadata(dest_conn, self.target_table.get())
		
		# 列数量检查
		if len(src_columns) != len(dest_columns):
			raise ValueError(f"列数量不匹配 源表:{len(src_columns)} 目标表:{len(dest_columns)}")
		
		# 逐列对比
		for src_col, dest_col in zip(src_columns, dest_columns):
			s_meta = src_meta[src_col]
			d_meta = dest_meta[dest_col]
			
			# 类型兼容检查
			if not self.is_type_compatible(s_meta['type'], d_meta['type']):
				raise ValueError(f"列类型不兼容 {src_col}({s_meta['type']}) => {dest_col}({d_meta['type']})")
			
			# 长度检查
			if s_meta['length'] > d_meta['length']:
				raise ValueError(f"列长度不足 {dest_col} 最大长度:{d_meta['length']}")
	
	def get_columns_metadata(self, conn, table_name):
		"""获取列元数据"""
		query = f"""
            SELECT
                COLUMN_NAME,
                DATA_TYPE,
                CHARACTER_MAXIMUM_LENGTH,
                NUMERIC_PRECISION,
                NUMERIC_SCALE
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_NAME = '{table_name}'
            ORDER BY ORDINAL_POSITION
        """
		meta = {}
		with conn.cursor() as cur:
			cur.execute(query)
			for row in cur:
				meta[row.COLUMN_NAME] = {
					'type': row.DATA_TYPE,
					'length': row.CHARACTER_MAXIMUM_LENGTH or row.NUMERIC_PRECISION or 0,
					'scale': row.NUMERIC_SCALE or 0
				}
		return meta
	
	def is_type_compatible(self, src_type, dest_type):
		"""类型兼容性检查"""
		type_map = {
			'int': ['int', 'bigint', 'smallint'],
			'varchar': ['varchar', 'nvarchar', 'text'],
			'datetime': ['datetime', 'datetime2', 'date'],
			'decimal': ['decimal', 'numeric', 'float'],
			'bit': ['bit']  # 添加 bit 类型映射
		}
		
		for group in type_map.values():
			if src_type in group and dest_type in group:
				return True
		return False
	
	def start_sync(self):
		"""启动同步任务"""
		frequency = self.frequency.get()
		
		if frequency == "立即执行":
			self.toggle_controls(False)
			threading.Thread(target=self.sync_data).start()
		else:
			self.schedule_job(frequency)
			self.toggle_controls(False)
			self.start_scheduler()
	
	def schedule_job(self, frequency):
		"""设置定时任务"""
		schedule.clear()
		time_str = self.schedule_time.get()
		
		try:
			if frequency == "每天":
				schedule.every().day.at(time_str).do(self.sync_data)
			elif frequency == "每小时":
				schedule.every().hour.at(time_str.split(':')[1]).do(self.sync_data)
			self.log(f"定时任务已设置：{frequency} {time_str}")
		except Exception as e:
			self.log(f"定时设置失败：{str(e)}", error=True)
			messagebox.showerror("错误", f"时间格式错误：{str(e)}")
	
	def start_scheduler(self):
		"""启动定时器"""
		self.scheduler_running = True
		
		def run_scheduler():
			while self.scheduler_running:
				schedule.run_pending()
				time.sleep(1)
		
		threading.Thread(target=run_scheduler, daemon=True).start()
	
	def stop_sync(self):
		"""停止同步任务"""
		self.scheduler_running = False
		schedule.clear()
		self.toggle_controls(True)
		self.log("同步任务已停止")
	
	def toggle_controls(self, enabled):
		"""切换控件状态"""
		state = tk.NORMAL if enabled else tk.DISABLED
		self.start_btn.config(state=state)
		self.stop_btn.config(state=tk.DISABLED if enabled else tk.NORMAL)
	
	def log(self, message, error=False):
		"""记录日志"""
		timestamp = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
		log_msg = f"[{timestamp}] {message}"
		self.log_queue.put((log_msg, error))
	
	def process_log_queue(self):
		"""处理日志队列"""
		try:
			while True:
				msg, error = self.log_queue.get_nowait()
				tag = "error" if error else "info"
				self.log_text.insert(tk.END, msg + "\n", tag)
				self.log_text.see(tk.END)
		except queue.Empty:
			pass
		self.root.after(100, self.process_log_queue)
	
	def load_config(self):
		"""加载配置文件"""
		config = configparser.ConfigParser()
		try:
			config.read(self.config_file, encoding='utf-8')
			
			# 源数据库配置
			self.src_server.delete(0, tk.END)
			self.src_server.insert(0, config.get('SOURCE', 'Server', fallback=''))
			
			self.src_database.delete(0, tk.END)
			self.src_database.insert(0, config.get('SOURCE', 'Database', fallback=''))
			
			self.src_username.delete(0, tk.END)
			self.src_username.insert(0, config.get('SOURCE', 'Username', fallback=''))
			
			self.src_password.delete(0, tk.END)
			self.src_password.insert(0, config.get('SOURCE', 'Password', fallback=''))
			
			# 目标数据库配置
			self.dest_server.delete(0, tk.END)
			self.dest_server.insert(0, config.get('DESTINATION', 'Server', fallback=''))
			
			self.dest_database.delete(0, tk.END)
			self.dest_database.insert(0, config.get('DESTINATION', 'Database', fallback=''))
			
			self.dest_username.delete(0, tk.END)
			self.dest_username.insert(0, config.get('DESTINATION', 'Username', fallback=''))
			
			self.dest_password.delete(0, tk.END)
			self.dest_password.insert(0, config.get('DESTINATION', 'Password', fallback=''))
			
			# 表配置
			self.source_table.delete(0, tk.END)
			self.source_table.insert(0, config.get('TABLES', 'Source', fallback=''))
			
			self.target_table.delete(0, tk.END)
			self.target_table.insert(0, config.get('TABLES', 'Target', fallback=''))
			
			# 定时配置
			self.frequency.set(config.get('SCHEDULE', 'Frequency', fallback='立即执行'))
			self.schedule_time.delete(0, tk.END)
			self.schedule_time.insert(0, config.get('SCHEDULE', 'Time', fallback='00:00'))
		
		except Exception as e:
			messagebox.showwarning("配置加载警告", f"配置文件读取错误: {str(e)}")
	
	def save_config(self):
		"""保存配置文件"""
		config = configparser.ConfigParser()
		
		try:
			config['SOURCE'] = {
				'Server': self.src_server.get(),
				'Database': self.src_database.get(),
				'Username': self.src_username.get(),
				'Password': self.src_password.get()
			}
			
			config['DESTINATION'] = {
				'Server': self.dest_server.get(),
				'Database': self.dest_database.get(),
				'Username': self.dest_username.get(),
				'Password': self.dest_password.get()
			}
			
			config['TABLES'] = {
				'Source': self.source_table.get(),
				'Target': self.target_table.get()
			}
			
			config['SCHEDULE'] = {
				'Frequency': self.frequency.get(),
				'Time': self.schedule_time.get()
			}
			
			with open(self.config_file, 'w', encoding='utf-8') as f:
				config.write(f)
		
		except Exception as e:
			messagebox.showerror("配置错误", f"配置文件保存失败: {str(e)}")
	
	def on_closing(self):
		"""关闭窗口事件处理"""
		self.save_config()
		self.stop_sync()
		self.root.destroy()


if __name__ == "__main__":
	root = tk.Tk()
	app = DatabaseSyncApp(root)
	root.protocol("WM_DELETE_WINDOW", app.on_closing)
	root.mainloop()


def read_config(file_path):
	config = []
	with open(file_path, 'r', encoding='utf-8') as f:
		for line in f:
			line = line.strip()
			if line:
				config.append(line.split(','))
	return config


def write_config(file_path, data):
	with open(file_path, 'w', encoding='utf-8') as f:
		for item in data:
			f.write(','.join(item) + '\n')
